package com.kaishengit.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.kaishengit.entity.Employee;
import com.kaishengit.entity.Files;
import com.kaishengit.util.DBHelp;
import com.kaishengit.util.RowMapper;



public class FileDao {
	private DBHelp<Files> db = new DBHelp<Files>();
	public List<Files> findFilesByFileTypeId(String id) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT tf.employeeid,tf.filetypeid,te.username,te.password,te.email,te.pic,");
		sql.append("tft.name AS ftname,tft.createtime AS fttime,tft.employeeid AS fteid,tft.projectid,tft.desc,");
		sql.append("tf.name,tf.id,tf.filename,tf.createtime,tf.filesize FROM t_file AS tf ");
		sql.append("LEFT JOIN t_employee AS te ON te.id = tf.employeeid ");
		sql.append("LEFT JOIN t_filetype AS tft ON tft.id = tf.filetypeid ");
		sql.append("WHERE tf.filetypeid = ?");
		return db.executeQueryForList(sql.toString(), new FileRowMapper(), id);
	}
	
	public boolean saveFile(String id, String realname, String newname,
			String createtime, Long filesize, String employeeid,
			String filetypeid) {
		String sql = "insert into t_file(id,name,filename,createtime,filesize,employeeid,filetypeid) values(?,?,?,?,?,?,?)";
		
		return db.executeSQL(sql, id,realname,newname,createtime,filesize,employeeid,filetypeid);
	}
	
	public Files findFileByFileName(String filename) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT employeeid,filetypeid,te.username,te.password,te.pic,te.email,");
		sql.append("tf.id,tf.name,tf.filename,tf.createtime,tf.filesize,tf.filetypeid ,(SELECT `name` FROM t_filetype WHERE id = tf.filetypeid) AS ftname ");
		sql.append("FROM t_file AS tf LEFT JOIN t_employee AS te ");
		sql.append("ON te.id = tf.employeeid WHERE filename = ?");
		return db.executeQueryForObject(sql.toString(), new FileRowMapper(), filename);
	}

	public boolean deleteFile(String id) {
		String sql = "DELETE FROM t_file WHERE id = ?";
		return db.executeSQL(sql, id);
	}
	
	
	private class FileRowMapper implements RowMapper<Files>{

		public Files mapRow(ResultSet rs) throws SQLException {
			Files f = new Files();
			f.setCreatetime(rs.getString("createtime"));
			f.setEmployeeid(rs.getString("employeeid"));
			f.setFilename(rs.getString("filename"));
			f.setFilesize( rs.getFloat("filesize"));
			f.setFiletypeid(rs.getString("filetypeid"));
			f.setId(rs.getString("id"));
			f.setName(rs.getString("name"));
			Employee e = new Employee();
			e.setEmail(rs.getString("email"));
			e.setId(rs.getString("employeeid"));
			e.setPassword(rs.getString("password"));
			e.setUsername(rs.getString("username"));
			e.setPic(rs.getString("pic"));
			f.setEmployee(e);
			f.setDocumentname(rs.getString("ftname"));
			
			return f;
		}
		
	}














	

}
